Tag Info

Hot answers tagged

7

Shrinking as in regularly maintenance shrinks the DBs? Remove that immediately, as in right now, before you continue reading. Why you should not shrink your data files. As for your question, there are far too many unknowns. Do you need disaster recovery, including geo-DR? Do you need a warm standby location? What is your desired recovery interval in case of ...


7

Try this on the master database: SELECT name, value, value_in_use, [description] FROM sys.configurations WHERE name like '%server memory%' ORDER BY name OPTION (RECOMPILE); Gives you max server memory (MB) and min server memory (MB)


5

If your goal here is to recreate the the logins with the same password then you are SOL, this is only stored in the tables/views in the master database. Brian's recommendation will work if the old instance is still around but if not then you will need to do one of the options below. Recreate the logins Use sp_change_users_login to map the database user to ...


5

Not quite the answer that you're looking for, but the name of the backup file can be found in a combination the backup* tables in msdb. Your restore process could query the production msdb, figure out the backup file name, and go from there. Aside from that, there's always powershell which gives you the perfect blend of file system and database access.


4

You should specify the FORMAT clause to overwrite your media set. See the following MSDN article for more details: http://msdn.microsoft.com/en-us/library/ms191304.aspx


3

Take a look at the following MSDN article on Troubleshooting a Full Transaction Log (Error 9002)


3

No mainstream RDBMS does, according to The Third Manifesto and other works Saying that, if you want "good enough for most purposes" then yes they do. The finer points of Codd's rule and relational theory don't put food on the table, frankly. And don't metter for 99%+ of databases in the wild.


3

I don't believe you have enough information about the users in the database to create the logins. The login-specific information resides in the master database, whereas the database user information (including mappings) resides in the individual databases. If the original server is available, there is a system stored procedure, called sp_rev_login, that ...


3

The MSSQL datetime data type can take values from January 1, 1753 to December 31, 9999. The integer equivalents for those values are -53690 and 2958463 respectively. Trying to convert integer values outside that range would cause the arithmetic overflow.


3

Revert the database back to multi user mode and try something like the following. alter database xyz SET single_user with rollback immediate dbcc checkdb('xyz',REPAIR_ALLOW_DATA_LOSS) Likely another process is grabbing the database connection before you get in there. Combining both statements should ensure you get that connection.


3

Essentially, you're trying to work around what appears to be somebody else's bad implementation. That seems reasonable to me, and sometimes, that's a necessity. If this program is indeed "crashing" when trying to connect to one database but not another, as opposed to, oh, you know, displaying an error message, that's pretty weak. I have a standby solution ...


3

Something like this may do the trick. I just quickly modified something I use for a slightly different purpose. declare @dbname varchar(80), @lastfull datetime, @fullback varchar(1024), @position int, @SQL nvarchar(max) set @dbname = 'YourDB' select @lastfull = MAX(backup_finish_date) FROM master.sys.databases d LEFT ...


3

In my experience, detach/attach is the fastest method. The bottleneck would probably be how quickly you could copy the files across the network. Assuming the two databases have identical Windows accounts (if you're using SQL accounts you may have to update SIDs), you could probably use something like this script that I have laying around from before I ...


3

I would attach the files to a new database, script the table and re-create it in your active database. First up, move the *.mdf and *.ldf files to the default database file locations. This way, they'll inherit the appropriate file level permissions. Now, create a new database from the existing files, but using a new name: USE master GO CREATE DATABASE ...


2

This is not a backup error: it's a database error in one of your database files. Backup can't read it and eventually you'll get it in normal operation. You mention error 823, It's nasty: a hard IO error To verify, run DBCC CHECK DB and see Paul Randall's site for "823". he wrote DBCC CHECK DB ...it says that an I/O operation failed at the OS level ...


2

No, you can't in the current form There are several options using UDFs or XML or dynamic SQL to pass in lists. The most comprehensive article on how to do this is "Arrays and Lists in SQL Server 2005 and Beyond" by Erland Sommarskog


2

Mirroring that is set up in high-safety mode with the presence of a witness can allow for a switch of the active database instance in a way that is seamless to the application. The catch is that it's a synchronous operation every time there's a transaction... meaning it must be committed on both nodes before the transaction is complete. Therefore, there is ...


2

Ah now that is the ultimate question! You will get 101 responses no doubt but if your question is actually asking which of these technologies is probably best suited to making a database available then I would go for Mirroring every single time. Replication is fantastic for scaling of data, and improving availability of subsets of data. If your question is ...


2

Please try this TSQL command, rather than your own: USE [PQR] GO GRANT SELECT ON [dbo].[XYZ] to ABC


2

What about setting up an Operator, configuring Database Mail, and having an email sent whenever the job runs?


2

The maximum character length of a database name in SQL Server is 128 characters for SQL Server 2008 R2. Therefore you can backup a database that has more than 50 characters with the BACKUP command in T-SQL (see below for example). You need to make sure your @name variable can hold more than 50 characters. Example: CREATE DATABASE ...


2

A post on Microsoft's web site suggests that you can work around this problem by disabling query prefetching for the linked server. In the ODBC Data Source Administrator panel on the server, configure your linked server from System DSN. Under the Performance tab, uncheck the box 'Enable pre-fetch of data for queries'. OK, OK. Delete and re-create your ...


2

The format for this is listed at the bottom of the syntax section of the web page referenced. <time_period> ::= number[minutes | hours | days | weeks | months] Here are the samples. number[minutes| hours| day| weeks| months] Specifies the time interval used to determine if a report or backup file is old enough to be deleted. number is an integer ...


2

While you certainly could use techniques like SSH port forwarding to make a remote listening TCP socket appear like a local one, it probably would not be of any help. If your client is "crashing" upon connection, it most likely would not stop doing so just because you are using a different destination IP address. There might be a myriad of reasons why the ...


1

Install latest SQL Server 2005 service packs This Connect article refers to a some issues with the SP2 install but you appear to being the RTM version. Latest is SP4


1

These are good practice jobs to clean up old database information older than 30 days. It's normally done prior to backup or other quiet period but can be at any time. Cleaning Jobhistory removes stored history of SQL Server jobs that have executed. Cleaning backuphistory removes old records from backup/restore history. Cleaning maintplan history may also ...


1

Check transaction log of system db (msdb, master, temp, etc)


1

THe table wasn't showing up in SMS 2005, so I used SMS 2008 and ran as Administrator, and then I was able to remove the table via the GUI.


1

Revert to sql server 2000 sp3 Upgrade to sql server 2005 Use this hack which PATCHES sqlservr.exe & definitely not supported by microsoft.


1

The syntax looks ok, make sure the database name doesn't need to be [square-bracketed] and that the logical names match.



Only top voted, non community-wiki answers of a minimum length are eligible