Posts Tagged "Query"

How to (re-)set identity column value in SQL Server?

Posted by on Mar 2, 2011 in SQL Server, Transact-SQL | 0 comments

When you use identity columns in SQL server you may have recognized that when deleting all table data your identity value stays at its latest position. Sometimes this is useful, sometimes it is not. Then you need to set this value to your preferred starting point. Another scenario would be that you need to start with a special position in a new table for example to meet naming conventions. All you need is the DBCC CHECKIDENT command. First you may want to know the value your identity column has at the moment: DBCC CHECKIDENT ('tablename', NORESEED) If you want to set the identity value to...

Read More

What to do with ‘suspect’ tapes in MS DPM 2007 and 2010?

Posted by on Feb 14, 2011 in Data Protection Manager | 4 comments

Sometimes it happens that Microsoft Data Protection Manager 2007, 2010 and 2012 mark a tape as ‘suspect’. Normally this should happen when 2 different tapes have the same barcode… If you do know that there are not 2 identically labeled tapes in your library then you can run the following from an elevated command shell on your DPM server: DPM 2012: osql -E -S localhost\MSDPM2012 -d DPMDB -Q "UPDATE tbl_MM_ArchiveMedia SET IsSuspect = 0" DPM 2010: osql -E -S localhost\MSDPM2010 -d DPMDB -Q "UPDATE tbl_MM_ArchiveMedia SET IsSuspect = 0" DPM 2007: osql -E -S...

Read More

How to display active connections and running queries in SQL Server?

Posted by on Nov 6, 2010 in SQL Server, Transact-SQL | 0 comments

This query shows all queries executed at the moment, the session id, status, used cpu time and the execution duration SELECT st.text, r.session_id, r.status, r.command, r.cpu_time, r.total_elapsed_time FROM sys.dm_exec_requests r CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS st The following query shows the number of connections a user has with a database SELECT db_name(dbid) as DatabaseName, count(dbid) as NoOfConnections, loginame as LoginName FROM sys.sysprocesses WHERE dbid > 0 GROUP BY dbid, loginame

Read More