Microsoft's SQL Server is a relational database management system (RDBMS) that runs as a server providing multi-user access to a number of databases. It originated from the Sybase SQL Server codebase, which is why both products use the extension of SQL called Transact-SQL (T-SQL).

learn more… | top users | synonyms (2)

1
vote
1answer
673 views

Doing a bulk insert in SQL Server

I have to do the insert operation in the db as follows, I create an item I create 5-10 properties for that item, all these data i get in the form of a collection of objects. I donot want to ...
1
vote
1answer
409 views

Read .sql file into a database

I created a sql database and filled it with some good data. Now I'm trying to move all the stored procedure over to another database instance. I exported the entire source database, including beloved ...
8
votes
1answer
372 views

Best tool to optimize SQL Server (not the queries, but the database itself)

Does anyone know what is the best tool to optimize an entire SQL Server database? I'm not talking about how to write the queries but more how to manage the database file, indexes, paging and ...
0
votes
1answer
164 views

Maintenance Plan produces BAK files I cannot use

I have a maintenance plan that does the following: Check Database Integrity Shrink Database Reorganize Index Rebuild Index Update Statistics Clean Up History Backup Database ...
2
votes
2answers
155 views

How can I prevent/detect that a end user restored a backup?

We need to prevent the SQLServer 2008 .mdf file from being replaced by the end user of our application. we collect data in the MDF that is important to us. If the user saves an old copy of the MDF, ...
4
votes
2answers
454 views

Personal Instance on Personal Computer

I finally got my hands on a copy of SQL Server 2008 R2 Developer Edition. I have installed an instance on my server at home. I wanted to get all the Management Tools (SSMS, Profiler, etc.) on my ...
1
vote
2answers
320 views

Best type to use in a composite key when one of the values varies in type

I am building a data store for the content of multiple blog sites which have been scraped. Each of these sites is going to have an entry in a Blog table BlogId Url ...
1
vote
4answers
5k views

Attaching database to the SQL server

I have the following error The database 'Test' cannot be opened because it is version 661. This server supports versions 655 and earlier. A downgrade path is not supported. Usually this kind ...
5
votes
3answers
704 views

Books on optimizing and refactoring SQL stored procedures

Can you please recommend resources to write efficient store procedures, refactor and optimize them? EDIT: - jcolebrand ♦ The intent of the answers to this post may be books or non-transient blogs. ...
10
votes
4answers
4k views

Help me choose a RAID level combination for a SQL Server 2008 instance

I am going to rebuild one IBM 3400 server from scratch. This server is dedicated to a SQL Server 2008 instance running on Windows 2008 R2. I am going to make new RAID configuration. I have 6 SCSI ...
3
votes
5answers
15k views

How to check the growth of database

I would like to know how to check the growth of entire database and individual data files. Also, what to suggest to the client if the database is growing rapidly?
7
votes
4answers
2k views

Slow temp table drops in sql 2005

I've run into a problem on our production sql server where temp table objects take a long time to drop (obvious when small and synchronous drop is used). I can't reproduce this on other sql servers ...
2
votes
2answers
3k views

Restore Database script

I have a live database which i would like to create a backup and restore script that will take the "live" database every night, create a back, then drop the existing "testing" database (if any) and ...
47
votes
4answers
29k views

When should a primary key be declared non-clustered?

While creating a test database for another question I asked earlier, I remembered about a Primary Key being able to be declared NONCLUSTERED When would you use a NONCLUSTERED primary key over a ...
1
vote
1answer
633 views

Does composite cluster index affect performance of seek/scan non-cluster index which is part of the composite?

I have bank transaction table with columns Division Brcode TrDate ProjectCode Accountnumber EffectiveDate DrCr Amount EndBalance In the other table the Brcode itself is unique and is assigned under ...
5
votes
3answers
910 views

Save Connections in SSMS?

Can I save some sort of session or profile in SSMS (2008 r2) that will open up connections to a saved list of database servers when I open SSMS? So for example I frequently will open up SSMS and ...
2
votes
1answer
3k views

Cannot browse for Backup files using SSMS [duplicate]

I'm facing a strange problem. When I try to backup or restore any database using a SQL Server user (not sa) that belongs only to "public" server role, i have the following error when i click the ...
3
votes
3answers
262 views

Is it possible to have 2 or more databases active, and synchronized between them?

edit I'm testing possible solutions for database redundancy and high availability,I tested mirroring it worked fine, but lacks scalability plus there will be a lot of work in one server. Now I ...
2
votes
2answers
1k views

How can an identity primary key index become fragmented?

From what I understand about index fragmentation, this should not be possible. The cases I have found in my databases are non-clustered. Example: ALTER TABLE [dbo].[ClaimLineInstitutional] ADD ...
5
votes
2answers
8k views

Unable to delete SQL Server 2008 R2 login

I had created a SQL Server login some days back. Now I need to delete the same. So I went into SQL Server Management Studio => Security => Logins and selected the login that I want to delete. It ...
0
votes
4answers
1k views

Execute Stored Procedure that cannot be manually stopped

I am looking for a solution to: Allow an admin to manually run a stored procedure Once the stored procedure runs the same admin cannot stop it using the "Cancel Executing Query" The stored procedure ...
3
votes
2answers
547 views

Transaction Log 16GB on 2MB database [duplicate]

Possible Duplicate: Why is My SQL Server Transaction Log Growing or Running out of Space? The transaction log in one of my client's databases is 16GB, when the database itself is only 2MB. ...
14
votes
5answers
4k views

Possible attack on my SQL server?

Checking my SQL Server log I see several entries like this: Date: 08-11-2011 11:40:42 Source: Logon Message: Login failed for user 'sa'. Reason: Password did not match for the login provided. ...
2
votes
1answer
796 views

Differentiating tables and views in INFORMATION_SCHEMA.COLUMNS

I have a view which selects information from INFORMATION_SCHEMA.COLUMNS, which seems to give me both columns and views. Is there an easy way to tell which rows are columns and which are views? Should ...
2
votes
2answers
2k views

Manual failover error, not in correct state

i'm doing some tests to build one database with one mirror database and one witness. so far, i'm failing in the first test, I want to kill the witness, then the Main database, and then put the Mirror ...
11
votes
1answer
256 views

Migrating from a physical database to a cloud database

What are the implications of moving a physical database created in Microsoft SQL server 2008R2, that makes use of advanced features like Full-Text Search on varchar(MAX) FILESTREAM BLOBS to SQL Azure? ...
6
votes
1answer
159 views

Replication problems after server upgrade

In transactional replication - first 8000 rows of snapshot copies to subscriber snapshot then acts like it completed successfully. Publication table has 21 million rows. Snapshot says completed ...
2
votes
2answers
679 views

How can I create new login in SSMS 2008?

When I attempt to create new login in MSSQL Server 2008, I get the following errors: I have tried everything I know, but I can't get it to work. What should I do and how I can solve these issues? ...
2
votes
2answers
128 views

State of the art self healing and self protection in DBMS

At the moment I'm writing a paper and I need some input about the state of the art of self healing and self protection in DBMSs. Does somebody read some interesting articles or books about this topics ...
2
votes
2answers
614 views

Rebuilding Index - sort in temp db

What is the significance of SORT_IN_TEMPDB when working with indexes on a table of significant size (20GB plus)? Where else does sorting take place?
1
vote
2answers
417 views

How do I add a constraint after I've truncated a table?

I am writing a script. The purpose of this script is to Get all foreign key constraints on a database. Truncate a table. Re-Add the foreign key constraints that were removed. I'm having trouble ...
1
vote
1answer
547 views

How to prevent big performance impact during index creation

I have an existing database that is missing some indexes. When I tried creating some the database server really suffered in CPU usage during index creation interfering with the regular workload. Is ...
5
votes
2answers
424 views

Get NULL statistics for a column in MS SQL Server

In MS SQL Server 2005, how can I extract statistical information about NULL fraction (or NULL count) for a given column? Same information is available in Oracle and PostgreSQL, and I heard that MS ...
5
votes
1answer
459 views

SQL 2008 Merge Replication and table-level data compression

What would happen if you enable data compression (either row or page-level) on a replicated table (merge replication) where the publisher is SQL 2008 Enterprise (Supports data compression) and the ...
3
votes
1answer
212 views

Connect tables in a cube based on historical data

So this may be the wrong place, and may seem pretty basic, but I'm new to building up cubes, so please forgive me. Imagine a setup described by the following: CREATE TABLE [dbo].[Orders] ( ...
4
votes
1answer
5k views

How can I monitor the call count per stored procedure per timespan?

In order to diagnose some performance issues I would like to get a better understanding of the number of times certain procedures are called compared to the system performance. Is there a way to get ...
3
votes
1answer
1k views

sp_whoisactive keeps returning create procedure statements

When I run sp_whoisactive on my server it often shows create statements being run for stored procedures but this doesn't make any sense since the procedure already exists. See the following example: ...
11
votes
2answers
413 views

To maximize DB performance, which commands should be run after loading large amounts of data into SQL Server 2008 via SSIS

I have written an SSIS package to load test data into an empty database. Some of the tables are very large (~700 million rows). Once the SSIS package has completed are there any commands I should run ...
4
votes
3answers
552 views

What are the pros and cons of using my customer codes as a primary key?

I am designing a database for one of my sales teams. Right now, the goal is to get a master list of customers and their mailing addresses, but the DB will eventually expand to track some other ...
3
votes
3answers
948 views

Restore script in SQL server doesn't work in W7

I need a little help with a restore process, I can't find specific help in other similar questions, sorry if my question does not correspond. I have this script in my system: RESTORE DATABASE ...
10
votes
2answers
912 views

Optimization: Moving variable declarations to the top of your procedure

While working on optimizing some stored procedures, I sat down with the DBA and went through some sprocs with high blocking and/or high read/write activity. One thing the DBA mentioned was I should ...
0
votes
1answer
230 views

How to find equivalent of Oracle's privileges in SQL Server?

I have a user in Oracle for which the permissions given are CONNECT and SELECT_CATALOG_ROLE. How do you find the equivalents when setting up a similar user in SQL Server?
1
vote
2answers
853 views

Restore System Databases from MDF/LOG files

We recently experienced a hardware failure that caused our SQL 2005 server to take it in the dump. The good news is that we have the MDF and LOG files for all the databases, the bad news is that we ...
4
votes
1answer
773 views

Property Owner is not available for Database 'DBNAME'

Property Owner is not available for Database 'DBNAME'. This property may not exist for this object, or may not be retrievable due to insufficient access rights. (Microsoft.SqlServer.Smo) I am ...
5
votes
2answers
155 views

Is is possible to manage SQL Server 2008 databases via R2 Management Studio?

Is it possible to manage SQL Server 2008 databases using SQL Server 2008 R2 Management Studio?
4
votes
4answers
684 views

Can two servers access the same database files?

I'm designing an application and considering some options regarding the database system since I'm not familiar with Microsoft SQL Server. I would like to know if it's possible to have 2 servers ...
3
votes
3answers
2k views

Are there ways to only replace SQL Server stored procedures when the definition has changed?

I know the following two patterns to write scripts for creating stored procedures, which can be executed repeatedly without throwing errors. if object_id('my_proc') > 0 drop procedure my_proc go ...
2
votes
2answers
287 views

SQL Server recovery with LVM snapshot as base

Is it possible to fully recover a database from a snapshot plus transaction log backups? By snapshot I mean a crash-consistent clone of the Server - in this case an LVM snapshot that is shipped ...
4
votes
1answer
2k views

How to deploy SSRS reports build through Report Builder?

I would be building or correcting the already build SSRS reports through report builder. Once the change is done in Development side, then how to publish the reports on test server/UAT or on ...
1
vote
2answers
687 views

Are there any reasons not enable SQLCMD-mode as default in SSMS?

I don't consider "just to ensure that sqlcmd-mode is not required by a script" a valid reason. Any things to think of before giving the order to enable this setting to my coworkers?