When the DB grew.
-
How can I find out when the database grew last ? Is it logged anywhere ?
Thanks.
-
There are some "undocumented" stored procs that let you format and print the transaction log. It would show the time of events like this. But, again, it's undocumented.
-
What about looking at the Modified Date on the MFD file?
-Dan
-Dan -
Isn't this date changed anytime a block is written to the MDF file?
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
-
Yes, the date on the MDF file is modified each time a block is written.
don1941 can you tell me what those undocumented stored procs are.
-
You could try a tool such as LogExplorer
Also, you could try DBCC LOG to look at the information but as of yet I have not found the details of understanding it, so not really usefull.
DBCC log ({dbid|dbname}, [, type={-1|0|1|2|3|4}])
where:
dbid or dbname - Enter either the dbid or the name of the database
type - is the type of output, and includes these options:
0 - minimum information (operation, context, transaction id)
1 - more information (plus flags, tags, row length, description)
2 - very detailed information (plus object name, index name, page id, slot id)
3 - full information about each operation
4 - full information about each operation plus hexadecimal dump of the current transaction log's row.
-1 - full information about each operation plus hexadecimal dump of the current transaction log's row, plus Checkpoint Begin, DB Version, Max XDESID
by default, type = 0
To view the transaction log for the master database, run the following command:
DBCC log (master)
-
Could I get some clarification on why the "Modified" date of the MDF file is not a valid indicator of the last time the database was expanded or shrunk?
Doing the following does not change the Modified date on the file:
1) Database has 1743MB free
2) create new table
3) insert 2 million rows
4) Database has 1687MB free
5) drop table
6) Database has 1743MB free
-At this point the Modified date is unchanged, but many blocks have been written.
-Dan
Edited by - dj_meier on 12/10/2002 4:54:32 PM
-Dan -
It can mean various things. I have a small DB here and can't really test based on transactions that well. However, if I stop the server the files modified dates are updated. So it is not just file size changes that can alter it for sure.
-
quote:
don1941 can you tell me what those undocumented stored procs are.No, they're undocumented.
I found a post here a while back and ran the DBCC command but the results were fairly cryptic. It had tons of info in it though. (It was DBCC, not a stored proc now that I think harder about it.)
-
I am really amazed that anyone even knows the DBCC(LOG) command anymore. It was common in the early life of SQL Server, but I haven't seen mention of it since 4.2 That command was well documented and explained in books, back around then, If I can dig up one of my old manuals, I'll post the details of it.
Antares686 gives an terrific breakdown on its usage. I was unaware of the other options than 3 for type, though I use it often.
Edited by - scorpion_66 on 12/11/2002 07:05:32 AM
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply