How to flush database to it's filegroup datafile?

[baddos]

My SQL server has 3gb of ram and our database is only 1.2gb. Right now SQL has the whole database loaded into memory which is great, but I'm worried about it's writes.

The last time the datafile was "modified" is yesterday, but there are tons of writes to the tables ever minute. Is there a way to force the server to flush data to the file without shutting down the database?

 

[mrdenny]

CHECKPOINT would be the command you are looking for.

Ignore the Last updated that windows reports. It's meaningless in the SQL world.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

Donate to Katrina relief

 

[baddos]

Hmm... Well I ran that command from query analyzer on my databse, but it still didn't update the modified date on my .mdf file. Maybe it worked, but just windows is being weird?

Also, this is a SQL 2000 Enterprise server running on a cluster with a shared disk.

 

[mrdenny]

SQL doesn't always update the modified date of files on the drive. Everything is written into the files. If it wasn't then you would have gotten errors when writting into the database.

The SQL Server will automatically checkpoint about once per minute.

I've got several SQL Servers where mdf and ldf files are dated from over a month ago.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

Donate to Katrina relief

 

[baddos]

Well it's just weird too because EM says that the space available inside the file is 0%. I would figure that SQL would have to grow the file and then would definately need to change the modified date.

I have to reboot the server soon for some Win2k3 patches anyways, so I'll see what it does after that.

 

[mrdenny]

Enterprise manager can be wrong about the free space when looking at the database properties. Right click on the database, select view then taskpad. That will give you a little bar graph at the bottom for each database file.

Growing the file doesn't nesassarly change the date modified on the file system. As I said above everything you know about file dates goes out the window with SQL Server.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

Donate to Katrina relief