[Articles] It Happens |
| Posted: 24 Sep 2013 11:00 PM PDT It happens. Code breaks. |
| You are subscribed to email updates from SQLServerCentral.com Articles tagged Editorial To stop receiving these emails, you may unsubscribe now. | Email delivery powered by Google |
| Google Inc., 20 West Kinzie, Chicago IL USA 60610 | |
[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. |
| 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? |
| 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 | |
[SQL 2012] Force T-SQL Job Step to Run Under Specific SQL Account |
| Force T-SQL Job Step to Run Under Specific SQL Account Posted: 25 Sep 2013 12:17 AM PDT I have created a SQL user to run certain dbcc commands for us (DBCC CHECKTABLE in this case). This user is governed by resource governor for this reason. I made the user a sysadmin and also made it the owner of the job. What I found later, was that if the owner of the job is a sysadmin, it will execute in the context of the agent account. If the owner is not sysadmin, it will execute under the context of the owner.Does anyone have any ideas as to how I can get this job to execcute as this new SQL login instead of the agent, but still have it with server role of sysadmin? |
| Posted: 25 Sep 2013 01:56 AM PDT Hi,I have report that takes state as parameter and executes the stored proc and generate the report data.Since I need to run this report for all the 50 states in USA I created a subscription for each state and generating report for each state.But is there any way I can loop the report for all 50 diff states instead of creating a subscription for each state?Thanks. |
| Setting log shipping in AlwaysOn environment Posted: 23 Jan 2013 03:58 AM PST Hello everyone, We have 4 Servers which have SQL SERVER 2012 and "AlwaysOn" have been enabled on all 4 servers: Server1,Server2,Server3,Server4 Server1 is the Primary node and thr rest are secondaries. There is a Sync relation between Server1 and Server2 and also there is aSync relation between Server1 and Server3 & Server4. Is it possible to setup log shipping from Server2 & Server3(secondaries) to two new servers?Does this have any problem? Thanks |
| I need an exclusion query again. My totals are wrong. Posted: 24 Sep 2013 08:41 PM PDT Hi Can someone put the following perhaps in a query that makes sense. My totals are wrong so I'm thinking that I need a better built query.'From Item Range CL00000 to 999999. Then exclude 001920, 001921, CL01926, CN00229 and CN00230'Kind regardsFred |
| Posted: 28 Aug 2013 06:19 PM PDT Hi Team Require your help on TRANSACTION CONTROL IN SSIS , We have created package with 12 dataflow task in one sequence container based on the sequence. All the dataflow task has SSIS framework implemented , when ever a record does not match certain condition that particular record moves into the error/reject log table but package does not fail it moves to the next record , However my requirnment is if any record move to error/reject log table than that particular record to be rolled back & cannot commit and then pick another record.Package will not fail but rollback has to be implemented for all the records that get captured in error/reject log table Thanks & RegardsRaga |
| How to set user permissions for service Posted: 24 Sep 2013 08:35 AM PDT I have two services that I am creating. The first one is continuously adding rows to a single table and the second will look at a different table in the db to see if there is any work to be completed. If there is any work to be completed the service will update the rows in that table. I need each of these two services to be able to execute side by side.My question is this. How can setup user permissions so that a user has read/write on just one table and read only on the rest of the database?Thanks,Gary |
| Posted: 24 Sep 2013 08:40 AM PDT I am working on a SSIS ETL project where I need to transfer data between two different networks-one has its own DMZ. So the data will travel through internet. Both servers are encrypted and secure. I have two concerns here:How do I establish/configure a connection between these two servers that are completely in different networks? Also the data in flight needs to be encrypted or in other words need to have encrypted or the connection between the two servers need to be connected- how do I accomplish that?Thanks. |
| You are subscribed to email updates from SQLServerCentral / SQL Server 2012 / SQL 2012 - General To stop receiving these emails, you may unsubscribe now. | Email delivery powered by Google |
| Google Inc., 20 West Kinzie, Chicago IL USA 60610 | |
