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 |
|
dhw
Constraint Violating Yak Guru
332 Posts |
 Posted - 2008-07-02 : 13:06:41
|
| Hello -I would like to retain the data from the SQL Dynamic Managment Views (DMVs) in case of server restart. My initial thought was to create my own set of tables and create a stored proc (scheduled to run a few times per day) to query the DMVs and then write the data my own set of tables in another database. Just curious what others think of this idea. One of the reason I would like to persist the data is to have some historical data for looking at the server as far as performance, queries, etc.Thanks - will |
|
|
maninder
Posting Yak Master
100 Posts |
Posted - 2008-07-02 : 13:22:29
|
| select * into #tempRequest2 from sys.dm_db_index_physical_stats(NULL,NULL,NULL,NULL,NULL)OR Select * into #tempRequest from sys.dm_db_exec_requestsManinder |
 |
|
|
dhw
Constraint Violating Yak Guru
332 Posts |
Posted - 2008-07-02 : 13:27:43
|
| Thanks. My plan is to do something similar (select from the dmv's into my own tables). I was also curious if anyone has had the need to save the DMV data for similar purposes. - will |
|
|
|
maninder
Posting Yak Master
100 Posts |
Posted - 2008-07-02 : 13:53:35
|
| They contain Dynamic Data.. so unless you are in a HEAP of Performance/Security Trouble, dont do that..Sure you can keep the Processes/DB Growth/Performance(Index) under check with these kind of Tables.. they can be used in Place..E.G. To see the recent Query Executed.select qs.text, ec.* from sys.dm_exec_connections ecCROSS APPLY sys.dm_exec_sql_text(ec.most_recent_sql_handle) qsManinder |
|
|
|
dhw
Constraint Violating Yak Guru
332 Posts |
Posted - 2008-07-02 : 14:04:42
|
| Well, part of the reason I want to retain some of the dmv data is in case of server restart, since that operation clears out the DMVs and I wouldn't have anything to go back to for a historical view. |
|
|
|
maninder
Posting Yak Master
100 Posts |
Posted - 2008-07-02 : 14:20:02
|
| Do you do FULL Databasesbackups and Transaction Logs (frequently).If you have a SOLID Backup Plan in place, nothing to worry about.... Only the worst case scenario is someone Pulled the Plug off the server and your Active Transaction is Corrupted.Then you have to take a Trickier approach to Rectify the issue...consider Reading this:http://blogs.msdn.com/psssql/default.aspxwww.sqlskills.comLots of Information about INTERNALS.Maninder |
|
|
|
dhw
Constraint Violating Yak Guru
332 Posts |
Posted - 2008-07-02 : 14:24:31
|
| thanks for the info and suggestions. |
|
|
|
|
|
|
Archive Dynamic Mgmt View data...