Saturday, June 8, 2013

[MS SQL Server] Does a backup cause TempDB to grow

[MS SQL Server] Does a backup cause TempDB to grow

Does a backup cause TempDB to grow

Posted: 08 Jun 2013 12:54 AM PDT

I received an alert this morning that our Temp drive had run out of space. When investigating it, I found that no large jobs had run outside of a weekly FULL backup databases job, which backs up about ten databases varying in size from 50GB - 2 TB.Do backups (if large enough) cause TempDB to grow?Thanks!

Any Tool / Application to read SQL Server Database Fragramentation ?

Posted: 07 Jun 2013 06:50 AM PDT

Hi I was wondering does anyone know of any tools that are available for SQL Server 2008 R2 to see internal fragmentation of a database. I'm interested in the logical fragmentation. Our Datafiles are allocated at a set size, so I believe there would not be much the OS File level fragmentation -- but we deleted half our data in the alllocated Datafiles and I wanted to see how the fragmentation is. (SQL Server side.. indexes have been full rebuilt) thanks

Partitioned Views

Posted: 07 Jun 2013 09:37 AM PDT

I have been assigned a database that has been allowed to grow absolutely out of control. Several of the main tables have over 15 million records with 50+ columns across! Oddly enough, this isn't a very efficient database to work with (sigh). Since this data is accessed constantly, and by various applications I'm afraid getting it somewhat manageable will be a daunting task.I have never created or worked with partitioned views before and I'm wondering if someone could share their experience with these or if anyone has a better idea as to where to even start. We are on SQL 2008 Standard edition. TIA!!

Issues with Adding a new subscriber to an existing transactional replication setup

Posted: 07 Jun 2013 07:15 AM PDT

I am trying to add a new subscriber to an existing transactional replication with pull subscription. Steps followed: At the publisher: 1. Changed the value for allow_initialize_from_backup to true using execute sp_changepublication 2. Create the backup of publication database 3. Restored the backup on the subscriber daatbase 4. At the publisher on the publication database, exeucted the following script to add the subscription use [subscription_db] exec sp_addsubscription @publication = N'Trans', @article = 'all', @subscriber = N'subscriber-server', @destination_db = N'testdb', @sync_type = N'initialize with backup', @backupdevicetype = N'Disk', @backupdevicename = N'C:\testdb.bak', @subscription_type = N'pull', @update_mode = N'read only' GO 5. At the subscriber server on the subsribing database, executed the following script to add pull subscription use [testdb] exec sp_addpullsubscription @publisher = N'publisher-server', @publication = N'Trans', @publisher_db = N'testdb', @independent_agent = N'True', @subscription_type = N'pull', @description = N'', @update_mode = N'read only', @immediate_sync = 0 go exec sp_addpullsubscription_agent @publisher = N'publisher-server', @publisher_db = N'testdb', @publication = N'Trans', @distributor = N'distributo-server', @distributor_security_mode = 0, @distributor_login = N'xxx', @distributor_password = 'xxxx', @enabled_for_syncmgr = N'False', @frequency_type = 64, @frequency_interval = 0, @frequency_relative_interval = 0, @frequency_recurrence_factor = 0, @frequency_subday = 0, @frequency_subday_interval = 0, @active_start_time_of_day = 0, @active_end_time_of_day = 235959, @active_start_date = 20130606, @active_end_date = 99991231, @alt_snapshot_folder = N'', @working_directory = N'', @use_ftp = N'False', @job_login = null, @job_password = null, @publication_type = 0 GO Everything looks fine till now, and when i synchronize the subscription, i am running into an errors like Cannot update identity column col1 Could not find stored procedure 'sp_MSins_dboTABLE' Can you guys please help
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog