Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
DLTaylor
Posting Yak Master
136 Posts |
 Posted - 2007-03-01 : 09:02:40
|
| I'm fairly new to SQL administration and have a few questions;I use SQL 2000 (sp3) (400 GB hard drive)I have a database with an *.mdf file standing at 145 GB.The *.mdf has a last modified date of 29/03/2006 (nearly 1 yr ago)I have a 2.5 GB *.ldf file (same database).The *.ldf was last modified 22/02/2007.My questions are;Should the *.mdf have a more recent modified date? If, for example, I ran DBCC SHRINKDATABASE command - it would give me a modified date of today. Should I be doing this? & are there any other tasks I should be running which would leave me with more frequent modified dates?Should the *.ldf file have a more recent date? Should I be running any commands e.g. DBCC SHRINKFILE would reduce log file to 2mb.Or as everything is working finr should I be happy?I have just taken a full backup of the database – is there anything you could recommend I do next?Thanks for your time |
|
|
mcrowley
Aged Yak Warrior
771 Posts |
Posted - 2007-03-01 : 10:04:25
|
| What does the Windows modified date matter? The difference is caused by the fact that the file is open, and not all modifications have been made yet (at least in Windows' eyes). Pay no attention to that date, it is misleading, and non-productive.As for whether you should shrink the database or not: don't. You have plenty of free diskspace, so you do not need any of the space that would be freed up.For the backups, if it has not been done yet, schedule the full backup to be on an automatic schedule. Check to see if the database is in FULL or SIMPLE recovery mode. If it is in FULL recovery mode, also schedule transaction log backups. |
 |
|
|
DLTaylor
Posting Yak Master
136 Posts |
Posted - 2007-03-01 : 12:02:46
|
| What you said makes good sense – thank you.On a slight detour – I have a TempDB standing at 14GB of data.Looking at the tables inside the DB, I would predict the data does not add up to 14GB.How can I confirm this?If I have space – how would you recommend me turning this into space available on my hard drive? (Safely) |
|
|
|
mcrowley
Aged Yak Warrior
771 Posts |
Posted - 2007-03-01 : 13:05:27
|
| Tempdb is used for more than just tables. Or at least tables you can see. Tempdb is where data is joined and sorted. It is also where temporary tables and to an extent table variables exist until they are dropped. Some index creation operations also take place in tempdb. You can try to shrink it back, but I bet it will come back to 14GB after the applications have been pounding the database for a while. |
|
|
|
TonyTheDBA
Posting Yak Master
121 Posts |
Posted - 2007-03-05 : 08:44:40
|
quote:
You can try to shrink it back, but I bet it will come back to 14GB after the applications have been pounding the database for a while.
Or even before that, we have a network appliance that manages our web filtering and its based on a SQL2K backend. Currently the applciation DB is 18GB and the TempDB is 12 GB. As we have run out of disk space on the appliance, we needed to recover some diskspace, so I suggested stopping the service, deleting TempDB and restarting the service. The guys did this . . . and TempDB was recovered at 12GB!First time I've seen anything like this . . . Model ISN'T 12GB incidentally  . The only thing I can think of is that there is a startup procedure that expands tempdb to x Proportion of the Application DB. The point of doing this does escape me somewhat, but as its a managed applicance I can throw this 'opportunity' in the direction of another DBA  -- RegardsTony The DBA |
|
|
|
|
|
|
|
|
*.ldf and *.mdf files