[MS SQL Server] TEMPDB is growing to 400GB |
- TEMPDB is growing to 400GB
- MSSQL Vulnerabilities
- Backup users and permissions before database restore
- Backup of msdb stuck in rollback
- Checkpoint process
| Posted: 08 Oct 2013 05:15 AM PDT In our production server, tempdb is growing suddenly to 400GB (Earlier, it will reach maximum 210GB). There is no changes at server level or database level.But, It is not happening in another server where the server configuration is same as production server.Below the more information,Server is SQL server 2008 R2 and this server always executing some stored procedures (15 sessions simultaneously) which will push data into web server. Can someone help? |
| Posted: 08 Oct 2013 04:32 AM PDT Do we have to review monthly microsoft security bulletin to identify vulnerabilities affecting sql server ? If so do we have to apply them every month on the server? Please clarify.My current sql edition : SQL server 2008R2 SP2 EnterpriseWindows: Windows server 2008R2 SP1 |
| Backup users and permissions before database restore Posted: 07 Oct 2013 05:46 AM PDT Hi,We are refreshing our test environment daily from our production backup. But the users are loosing their permissions in dev environment. Can anyone suggest me how to automate the process of user and their permission backup and execute the permission script post -refresh ?I have a query that takes the user, role permissions for a particular database, i need to automate this process. Please post your suggestions.thanks in advance |
| Backup of msdb stuck in rollback Posted: 08 Oct 2013 03:02 AM PDT A native full backup of msdb and a Tlog backup on a SQL Server 2005 Enterprise instance (9.0.4053) deployed on a two-node Win2K3 cluster (Microsoft Windows NT 5.2 (3790)) are currently stuck in rollback. The msdb DB is in Simple recovery mode but the Tlog is full. The user DB is in full recovery mode. Attempts to add secondary Tlogs are failing due to a 3023 error (regarding the action needing to be serialized). Options I'm considering are moving non DB files off the volume to allow for autogrow of the Tlog and instance restart. I've tried changing the state of the DB from multi-user to single-user but the SPID stuck in rollback is blocking. SPID 126 is the session in question below.Are there other options I can consider?[code]session_id status blocking_session_id wait_type wait_time wait_resource transaction_id1098 suspended 0 TRACEWRITE 828 25828712297100 suspended 0 WAITFOR 687 0116 suspended 0 WAITFOR 1031 0126 suspended 0 BACKUPTHREAD 1611623843 0201 suspended 441 LCK_M_U 736093 DATABASE: 15 [BULKOP_BACKUP_LOG] 0249 suspended 0 TRACEWRITE 62 41659048209323 suspended 0 WAITFOR 1625 0418 suspended 0 PAGEIOLATCH_SH 0 18:1:2809411 43527231806441 suspended 0 BACKUPTHREAD 1307928843 0500 suspended 0 TRACEWRITE 390 43040835468[/code] |
| Posted: 07 Oct 2013 08:44 PM PDT Hi AllI've seen it posted all over the web that using the checkpoint pages/sec counter is one of the counters used to check for memory pressure.This makes no sense to me because the checkpoint process never actually removes pages from memory, it takes dirty pages, writes the changes to disk and marks the page as clean. As far as I understand, this page still exists in memory. Is my understanding correct?Thanks |
| You are subscribed to email updates from SQLServerCentral / SQL Server 2008 / SQL Server 2008 Administration To stop receiving these emails, you may unsubscribe now. | Email delivery powered by Google |
| Google Inc., 20 West Kinzie, Chicago IL USA 60610 | |
No comments:
Post a Comment