Wednesday, September 25, 2013

[MS SQL Server] Why is backup allowed on standby database

[MS SQL Server] Why is backup allowed on standby database

Why is backup allowed on standby database

Posted: 25 Sep 2013 05:08 AM PDT

Hi friends i know this is stupid question but i wanted to know why isn`t backup allowed on database that are in restoring mode or that are in standby mode.Why when database is in standby mode it is written standby and readableI heard that in 2012 database which are in standby mode or restoring mode , backups are allowed.if true why difference.

Virtual memory

Posted: 24 Sep 2013 06:37 AM PDT

Is virtual memory measured in terms of the size of pagefile.sys file?

query to get permissions for a user on all databases

Posted: 25 Sep 2013 12:30 AM PDT

I want to monitor rights change on certain users with windows authentication and sql server authentication. I am planning to automate this task by running queries against catalog views and saving the results in tables and generating email alerts if any permissions changed for any user on any database.example:I have these five windows users (Wuser1, Wuser2, Wuser3, Wuser4, Wuser5) that are part of a group called MyWUsers. They are set to have membership = public, Server roles = public and User Mapping = public to certain databases.Another user AdUser (user type =SQL Server) has membership and Server roles set to public and sysadmin, User mapping set to db_owner and public on certain databases.A service account (user type = windows) has membership and server role set to public and user mapping set to db_owner and public on certain databases.Please provide a sample query that I can use to get their memberships, server roles, use mapping, default schema on all databases on db instance on sql server 2008. Thank you.

Piecemeal restore takes forever

Posted: 10 Jul 2013 10:42 PM PDT

Hi EveryoneI have a 1.1TB database with 700GB of data on a SQL Server 2008 R2 SP1 instance. Most data is row compressed and in 2 partitioned tables. A full backup is taken daily to 4 files on a NAS as part of the daily maintenance routine (which also includes index fragmentation, updating statistics and an integrity check directly before the full backup and in that order). The backup is also compressed.On a testserver I want to do a piecemeal restore, starting with the primary filegroup (the partitioned tables are in other filegroups). The primary filegroup file is 9GB and has about 3GB of data. The transaction logfile is 16GB. I know, with a restore, the transaction logfile is being zeroed which could take some time. On the other hand, the empty space in the primary data file is not being zeroed during a restore operation when Instant File Initialization is enabled. This is the case on my testserver. The problem is that the piecemeal restore takes a very long time, about 6 hours. I investigated and tried a few things to speed up the restore. Here they are:- Check if Instant File Initialization is enabled, it is.- Checking the performance of the whole chain (NAS, network, SAN), by restoring another backup with approximately the same size (3GB) from the same NAS to the same testserver over the same network. The restore was done in 2-4 minutes.- Checking the number of virtual logfiles, which is 41 and thus ok.- Take a partial backup and restore that one partially. This restore took 2-4 minutes. My conclusion: creating the mdf and ldf files is not the problem.- Altered the daily maintenance routine by adding a transactionlog backup after the integrity check and before the full backup, hoping that the transactions from the transactionlog caused by the earlier operations, are left out from the full backup. I'm not sure this makes sense, but to my understanding when SQL Server takes a backup, the transactions in the transaction log are also included for the redo/undo fase when restoring a the backup. Also, I opened the Performance Monitor to see disk activity during the restore operation. There is only disk activity during the beginning of the restore operation. Furthermore, when I execute sp_WhoIsActive during the restore operation, I see that the restore session is suspended (wait_info: BACKUPTHREAD) while network traffic stays at maximum. The CPU's are also processing 'something'. I'm beginning to suspect the restore operation is scanning every page in the full backup files somehow. Is there someone who knows if this is true or, if not, knows the true reason why the piecemeal restore is so slow?Thanks

SQL Server event alert - restore from UNC path.

Posted: 24 Sep 2013 10:20 PM PDT

Hi,I have a SQL Server event alert that fires after a database restore using error ID 18267. This works fine when using a local path, but not if a UNC path is specified in the script. Has anybody out there come across this before? I need to track restore activity and fire a SQL Agent job after each successful restore.I've trawled [url=http://technet.microsoft.com/en-us/library/cc645603(v=sql.105).aspx][/url] and the only error ids I can see related to successful restore are:4356 - Restore is complete on database '%ls'. The database is now available.18267 - Database was restored: Database: %s, creation date(time): %s(%s), first LSN: %s, last LSN: %s, number of dump devices: %d, device information: (%s). Informational message. No user action required.18268 - Log was restored. Database: %s, creation date(time): %s(%s), first LSN: %s, last LSN: %s, number of dump devices: %d, device information: (%s). This is an informational message. No user action is required.18269 - Database file was restored. Database: %s, creation date(time): %s(%s), file list: (%s), number of dump devices: %d, device information: (%s). This is an informational message. No user action is required.None of these fire when restoring from UNC path.Thanks,Chris

Install - SQL Server 2008 R2 SP1

Posted: 24 Sep 2013 09:54 AM PDT

Hello guys,I when i try install SQL Server 2008 R2 P1 Build 7602 on Windows Server 2012 R2 Standard, I have this problem:Tried Get help online option but it is says No solution found, but setup starts when I select Run the program without getting help.both 64bitssomeone help me please?

SQL SERVER 2008 R2 Standard Edition Encryption?

Posted: 26 Feb 2013 10:14 PM PST

any option in sql server 2008 r2 standard edition can encrypt a database, other than changing the application?

Report Services taking up a lot of memory

Posted: 24 Sep 2013 11:55 AM PDT

Hey guys, I've got an issue on my production GP server. ReportingServicesService is taking up 12GB by itself, and growing very fast. How can I limit how much memory I can allocate to SSRS?
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog