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).
2
votes
1answer
40 views
Does Database Detach or Offline Clear the Cache Quickest?
A buddy of mine told me today that instead of bouncing SQL Server, I could simply detach and then re-attach a database and this action would clear the given database's pages and plans from cache. I ...
0
votes
1answer
20 views
Configure SQL Server Database for High Availability & Warehousing
I have 3 DB servers (DB1, DB2 & DB3 - all on Win 2008 R2 with SQL Standard 2008 R2 in the same datacenter). Let's assume AdventureWorks as an example database.
My goal:
- Setup DB mirroring with ...
-2
votes
1answer
41 views
Select query using a map [closed]
CREATE TABLE grid_rows(
[row_id] [int] NOT NULL,
[column_id] [smallint] NOT NULL,
[column_val] [decimal](18, 6) NULL
)
// (row_id, column_id) is a composite primary query
For a given ...
5
votes
1answer
36 views
Index usage stats DMV indicates no index activity
I'm trying to obtain information on indexes. According to several websites, the below TSQL query should join information on indexes to the indexes table, returning indexes and related information. ...
1
vote
3answers
53 views
Database design for conditional detail table
I have an expense management system for monthly expenses. Expenses are of two types: one-time monthly expenses, and daily expenses for each day of month. I have created the following tables:
...
0
votes
1answer
45 views
Simplify subqueries with join
I'm trying to create a report in SQL Server Reporting Services that shows the availability of multiple sites. The sites are monitored by a tool that uses a SQL Server 2012 database to store the data.
...
0
votes
1answer
36 views
PowerShell based SQL script execution [closed]
I am new to powershell. Even I dont think I know the basics..But now I am assigned a work to do the execution of sql scripts residing in a remote server.
1) we have to connect to that particular ...
0
votes
0answers
13 views
How to delete orphaned records from sysmergesubscriptions table?
I have some old orphaned records for old subscriptions that I want to delete from the sysmergesubscriptions table.
Each time I delete unnecessary records, it show up again after it syncs with any ...
0
votes
0answers
12 views
How to properly clean Merge Replication Setup
What is the proper way to completely clean Merge Replication setup ?
I have tried to delete the subscriptions, then delete the publication then "Disable Publishing & Distribution" & delete ...
3
votes
2answers
23 views
Given two known statements, how can I replicate deadlock?
I have two statements (an update against every row in one table (call it table A) and a delete on another table that looks up rows in table A) that I know are causing occasional deadlocks. It seems ...
-1
votes
1answer
34 views
Deleting large chunks of data
create table Grid(
grid_id,
//other columns
)
//grid_id is primary key
create table grid_info(
grid_id,
colulm_id,
column_vale
)
//grid_id is foreign key ...
0
votes
1answer
41 views
Update performance: clustered versus covering index
I have a simple, 3-column table with about 50 million rows in it. This table gets about 5,000 inserts/updates per second, and perhaps 20 queries per second are executed against the table. The table ...
1
vote
1answer
53 views
How to avoid empty rows in SSIS Excel Destination?
Does anyone have a way to avoid empty rows when using SSIS to export to Excel. Here's a simple example of one data flow task:
OLE DB Source:
Data Conversion (to handle the annoying UNICODE / ...
3
votes
1answer
84 views
Need to divide single column into multiple columns
My table has data with a single column:
1
2
3
4
5
6
7
8
9
10
And I want output like this:
1 2 3 4 5-first row
6 7 8 9 10 - second row
Another has two columns like this:
Column 1 ...
0
votes
1answer
30 views
how I can create check sum to validate no changes at int string pairs on table
Business case is.
1)Users print document on paper
2)users carry printed paper to Boss, Boss sing on paper
3)after that users going to administrator to digitally sign that paper.
My task is to help ...
1
vote
2answers
55 views
SQL Server Replication Alternative Software
We have used SQL Server Replication for a long time and had some issues with it that sometimes we needed to reinitialize subscriptions to fix some issues & other times we needed to destroy the ...
-2
votes
1answer
39 views
Errors keeping stored procedures through restore from backup
We are running SQL Server 2008 R2 on two identical servers. One, called LIVE, is our production environment. The other, called QA, is our dev enivornment. In order to check backup integrity and run ...
2
votes
1answer
37 views
How do these snapshot isolation level configurations interact on a SQL Server 2005 instance?
I started troubleshooting a tempdb issue we are having on SQL Server 2005 Enterprise Edition. A developer is getting an error that tempdb is out of space. Technically, the error is:
Transaction ...
2
votes
2answers
55 views
Transaction Log maintenance when switching to Simple Recovery
Background:
I recently inherited 50+ SQL Servers with 450+ databases. The nightly backups are roughly 8TB and, needless to say, we're using more disk space than we'd like. All of the databases are ...
9
votes
1answer
291 views
Stored procedures vs. inline SQL
I know stored procedures are more efficient through the execution path (than the inline sql in applications). However, when pressed, I'm not super knowlegeable about why.
I'd like to know the ...
2
votes
4answers
202 views
High CPU usage on SQL server - Slow queries [closed]
Our MS SQL Server is using about 95% of the CPU-power.
After a server (hardware) restart, or a SQL-Service restart, the usage is 0% and slowly increases over the course of 1-3 days. Depending on how ...
3
votes
0answers
26 views
High CPU usage on SQL server - Slow queries [duplicate]
Our MS SQL Server is using about 95% of the CPU-power.
After a server (hardware) restart, or a SQL-Service restart, the usage is 0% and slowly increases over the course of 1-3 days. Depending on how ...
1
vote
2answers
51 views
How to find memory usage by table? [duplicate]
I want to discover how much memory is being used by each table. Is this information available in DMVs or elsewhere?
3
votes
2answers
41 views
Weird SQL error
am seeing the error logs below on my SQL server, I'm not sure if they are related to trace flag 3604. I also see something to do with memory dump as well. Please advice.
15:15:53.81 spid594 ...
0
votes
0answers
37 views
Why does selecting top 1 from composite index DESC also used to partition by month not select the top value?
We have very large database table that I need to select the value of the max id. Given the table size SELECT MAX(id) FROM tableA will not perform well enough.
Table definition (with columns omitted ...
0
votes
1answer
43 views
SQL Server automatic transaction log truncation
Have looked through msdn but cant find answers to these:
Any help much appreciated:
In simple recovery mode:
Is there any TSQL or alternative functionality that will provide a notification either ...
1
vote
2answers
49 views
How to count number of consecutive events matching status of last event?
In MS-SQL Server 2008, I have a table of events in the following format:
Controller | ExecutionTime | Result
1 | 2012-09-24 09:00:00 | 0
1 | 2012-09-24 09:01:00 | ...
9
votes
3answers
121 views
Is backing a website by a SQL Server 2012 OLAP cube considered reasonable?
I have been tasked with architecting a solution for a large retail chain. They want to allow each of its 1.2 million customers to log on to a web site to see the distribution of recent purchases ...
2
votes
1answer
57 views
SQL restore duration
I am trying to determine the duration of backup restore. I executed a few restore command to restore some backups located on network share drive. Here is command and summary,
RESTORE DATABASE [test]
...
3
votes
1answer
66 views
Insert performance with Geography column
I've been tasked with inserting data into a SQL Server table with a geography column. I've found that my times for doing inserts (same data 1.5 million rows) go up increasingly.
I started out with no ...
2
votes
1answer
47 views
LDAP in SQL Server 2012
In our current system, we query the LDAP using SQL Server 2008R2 but are moving to SQL Server 2012 where we have hit a snag. According to http://msdn.microsoft.com/en-us/library/cc707782.aspx one of ...
3
votes
2answers
40 views
Recommended distributor architecture for transactional replication?
We are planning to create a Transactional replication on 2 nodes of SQL Server 2008 R2, each node will be a publisher and a subscriber.
What is the best practice in order to have this scenario done? ...
0
votes
1answer
46 views
EXPORT specific columns to INSERT statements
Is there a way to export specific columns and a fixed number of rows from a table to INSERT statements?
I am trying to generate these for unit testing based on a large production table so using ...
0
votes
2answers
71 views
Shrink transaction log while transaction in progress
I have a several-GB import into my SQL Server 2005 (Standard Edition) database.
The import is separated into 4 parts, each part in its own transaction.
As you can guess, the transaction log files ...
0
votes
2answers
71 views
Bulk Insert through netwok
Can someone help me with these?
BULK INSERT DATABESE01.dbo.TABLE01
FROM '\\COMPUTER01\FOLDER01\TextFile.txt'
WITH
(
FIELDTERMINATOR = ' ',
rowterminator = '\n',
tablock
)
The error ...
1
vote
1answer
32 views
Joining data from legacy database
I'm trying to figure out how to write this query.
Table 1 - Booking
BOOKING_ID | BOOKING_DATE | etc...
1 | 2013/01/01 |
2 | 2013/01/01 |
Table 2 - BOOKING_PROCEDURE_TYPE
...
1
vote
2answers
45 views
IDENTITY_INSERT is set to OFF?
Here is my script of my db ,
CREATE TABLE [dbo].[MyTable](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Code] [nvarchar](25) NULL,
[Name] [nvarchar](25) NULL,
[dob] [date] NULL ,
CONSTRAINT [PK_MyTable] ...
0
votes
2answers
48 views
Home rolled gridview search results shows rows that are supposed to be ignored [closed]
Note: I tried posting this on Stack Overflow but didn't get any good bites. I'm still researching this but wanted to see if any db czars can assist here.
I'm in the process of trying to troubleshoot ...
2
votes
1answer
48 views
Linked Server to Mirrored Database Fails
I've setup up a mirrored database on two servers Server1 and Server 2.
On Server3, I'm trying to setup a linked server that will work regardless of which server has the active database.
I'm using ...
5
votes
3answers
342 views
Best practices to encrypt all the data
I'm working in a project, where the client is afraid their own employees (ie.: junior I.T. guys who the client does not trust) querying the database to get sensitive information.
The database is a ...
3
votes
1answer
45 views
Subquery returned more than 1 value
In my database I have two tables: projects and comp_types. When a new project is created a trigger is hit which inserts a concatenated value in a column in the projects table.
A fully working SQL ...
1
vote
1answer
27 views
Is there any way to retrieve “Messages” in SQL Server through JDBC?
By "Messages" I mean the Messages tab in the following figure, which includes "Warning", "n rows affected" and in the figure below, the execution time. In JDBC, ResultSet class is used to retrieve the ...
1
vote
0answers
42 views
Postgres books for a Sql Server DBA [closed]
I have a team of Sql Server DBAs and Architects. One of our newer teams is building a product exclusively on Postgres. Now, I need to make sure that we have the knowledge base to support this project. ...
1
vote
0answers
34 views
dbo.M7_* objects (stored procs) in my production database. What product are these from?
I have inherited a database from a predecessor who is long gone... There are about a hundred stored procs in the database that all start with dbo.M7_ They appear to be something to do with source ...
0
votes
0answers
54 views
Database associative table
I have a coworker who's insisting on using associative tables to create nullable associations. For example, we have this table :
cm.accounts
----------
id:int (PK, identity)
...
sm.services
...
0
votes
3answers
35 views
Access to tables in SQL Server
I would like to know if there is a table where accesses to tables are saved, per user or per system.
I'm using both SQL Server 2005 and 2008.
4
votes
2answers
53 views
Frequency distributions segmented by a field
Some background:
I have a sample population data file. Each record in the data file has a frequency weight (FIELD NAME: wgt) indicating how many times the records needs to be replicated to get the ...
3
votes
2answers
37 views
User permissions for Microsoft SQL Server
Please advice to give permission for a SQL server user. Conditions are, this user must be able to create,delete,modify,drop the tables/view/procedure on user default schema(not dbo) not in any other ...
3
votes
1answer
35 views
Does the week day number changes according to localization in SQL Server?
Does the weekday number changes according to server/database regional configuration in SQL Server (2005+)?
3
votes
0answers
32 views
Severity 016 Backup virtual device fail
My backup on one of my servers is failing once a week. This is the alert I'm getting:
DESCRIPTION: BackupVirtualDeviceFile::PrepareToFreeze: failure on
backup device ...
