New answers tagged sql-server-2008
0
There's some lack of detail in the question; for example, it would be helpful to know:
What is the reason to combine the data together into a single database? It makes no sense to me if there isn't a further goal behind doing so. Perhaps there is a better way to make that end goal happen with fewer intermediate step(s).
Is there some kind of boundary ...
3
You can also take a look at this whitepaper written by the SQL CAT team. Take note of who all reviewed that document too, it is very well written.
The whitepaper will explain that since we are talking about data compression there is some data that will compress better than other. I believe the section on Application Workload has information for some of your ...
0
There is an interesting detail to this query that I did not spot at first. Thanks to Fabricio Araujo's answer I now see it: you are accessing two tables. I have never seen this kind of usage of the update statement before and I do not advise using it. I recommend you use the more intuitive join syntax per Fabricio's answer.
The likely cause is that the join ...
0
First of all, change the query to:
UPDATE t1
SET
costPercentage = ISNULL(t2.PaymentIndex, 1.0),
t2.TopUp_Amt = (ISNULL(t2.PaymentIndex, 1.0) - 1.0)
* ISNULL(dbo.table1.Initial_Tariff_Amt, 0.00),
Total_Tariff_Inc_t2 = ISNULL(t2.PaymentIndex, 1.0)
* ISNULL(dbo.table1.Initial_Tariff_Amt, 0.00)
FROM
dbo.table1 t1
inner join dbo.table2 ...
7
This query requires you to scan every row in the table because
I guess procodet or ProviderCode are not indexed
Even if they were indexed, you have a LEFT which is a function on a WHERE predicate
And you have COLLATE too which is effectively a function on a WHERE predicate
"a function on a WHERE predicate" means indexes won't be used
If you batch it ...
1
Your trouble is likely due to how migration was done.
Stuff shouldn't be attached to your user, unless you are considered the owner.
Schemas are there to help you separate tables by whatsoever makes sense.
Suppose you have one table of resources for the HR departement and want a separate one for the production department, while keeping both on the same ...
4
Some notes:
After rebuilding indexes, do not update all statistics!
REBUILD indexes, don't REORGANISE
An index rebuild will rebuild statistics anyway. A further update actually means you'll have worse statistics because of sampling ratio. You can rebuild column statistics though.
Also, it's worth comparing the server specs (RAM, CPU, Disk) and query ...
0
In my current project, shrinking is a weekly part of our lives. Not into production, of course, but our various testing environments. When your live DB can take a huge amount of space, much of which isn't needed on development and testing servers, it can be a feasible idea to parse the unnecessary information from the production DB. Then shrink it to some ...
0
Could it be that the database is used in a different way in the new location?
This is a common theme - different queries running in the database copy (perhaps reporting-style queries).
In this case you may want to drop the existing indexes and create new ones that support these new queries.
You'd first have to let them use the db on the new location for a ...
9
This is a classic case of why you should specify the schema name when accessing database objects. When it is unspecified and you're trying to access an object in a non-default schema then you're going to run into the issue that you're seeing right now.
The real fix is to change your application (or whatever querying agent you have right now causing the ...
3
As addition to @AdamWenger answer.
To create scripts for transfering to anothe schema you can use following script
select 'ALTER SCHEMA dbo TRANSFER '+s.name+'.'+t.name
from sys.schemas s
join sys.tables t on t.schema_id=s.schema_id
where s.name='erpadmin'
10
If you want to go back to using the dbo schema like you were in SQL Server 2000, you can move the table back into the dbo schema:
ALTER SCHEMA dbo TRANSFER erpadmin.tablename;
An alternative if you like having the non-dbo schema is to set your user's default schema to erpadmin then if you do not specify a schema, it will use that as default. (Members of ...
0
There's actually a really easy way to do this without installing SSRS or any other tools. I used SQL Profiler, ran the reports stated, and got the code which I copied below. You will want this copied in a scheduled job and have it send out the mail from within the job and you're done!
This is the query passed for the 'jobs history' report:
exec ...
3
A good scenario on where you would need to shrink a database file is to remove Virtual Log Files in your transaction log. VLF's can come about because of improper growth and sizing strategy, or just kind of creep up on you over time. One of the maintenance tasks we have is to monitor VLF's. If you find your database has a large number of VLF's then shrinking ...
0
When an index rebuilding operation is performed, it drops the index, recreates the index, and updates its statistic.
If I read the question correctly, your question of dropping indexes, recreating then update the statistics is pretty much the same as simply rebuilding the index.
Please note that index reorganization operation does not update the ...
-2
I suggest you to try free trial version of Devart's Data and Schema Compare Tools. If you like them in furute you can buy full professional version (it is not expensive).
More about the tools here:
SQL Data Compare - http://www.devart.com/dbforge/sql/datacompare/
SQL Schema Compare - http://www.devart.com/dbforge/sql/schemacompare/
0
Indexes fragmentation and statistics are not the only possible cause for a performance trouble.
Apart from this rebuild and reorganize are different process.
INDEX REBUILD: it drops the existing index and recreates the index. Can be done online or offline.
INDEX REORGANIZATION: it physically reorganizes the leaf nodes of the index. It is always online.
...
7
You'll know when you need to shrink your database files, because it'll most likely come from a requirement to reclaim disk storage. Even if there is a database that has a large amount of free space in it, I usually still like to keep it as such without shrinking. No matter what people try to claim that it'll never use all that space, unless the database is ...
4
There are some cases to be made for shrinking a database. The main one is where you've had a database for a long time, and just deleted a load of data, and know that you're not going to need the database to be that size again. Ever.
Every time you grow a database file, you run the risk that the file will be fragmented on the disk, leading to poorer ...
6
Clearly there isn't a nested CASE expression here.
Not in the query text, no. But the parser always expands CASE expressions to the nested form:
SELECT CASE SUBSTRING(p.Name, 1, 1)
WHEN 'a' THEN '1'
WHEN 'b' THEN '2'
WHEN 'c' THEN '3'
WHEN 'd' THEN '4'
WHEN 'e' THEN '5'
WHEN 'f' THEN '6'
...
4
My hunch is that the query is getting re-written somewhere along the way to have a slightly different CASE structure, e.g.
CASE WHEN column = 'a' THEN '1' ELSE CASE WHEN column = 'b' THEN '2' ELSE ...
I believe this is a bug in whatever linked server provider you're using (in fact perhaps all of them - I've seen it reported against several). I also ...
0
on your old server did you also have the SQL server Agent running under NT AUTHORITY\SYSTEM? You may need to specify an actual user
5
What is the backup and restore method?
It is called Piece Meal Restore and unrestored filegroups can be restored at a later time.
Is it possible to restore only the lamb filegroup?
Yes it is possible to restore only the lamb filegroup.
Below will show you - how you can do it.
Create a database called "FGTest"
create database [FGTest] on primary (
name = ...
0
I hitted in past with SQLServer 2008 (not R2). Microsoft said: It's an hard limit. There is no way to overcome it.
0
Oracle object names are all uppercase by default. The linked-server query must exactly match the case of all referenced Oracle metadata, such as schema and table names.
8
An object in a filegroup will use all datafiles in the filegroup. Any table in FG1 resides equally on Datafile1, Datafile2 and Datafile3. If you need to control placement you need to create distinct filegroups. To remove a file from a file group use DBCC SHRINKFILE EMPTYFILE.
0
No matter even if your data is spread over any number of data files but still you can use INFORMATION_SCHEMA.TABLES to get the list of tables.
TABLES
Overview The INFORMATION_SCHEMA.TABLES view allows you to get
information about all tables and views within a database. By default
it will show you this information for every single table and view ...
0
Books online includes several reasons you might not be able to drop an index.
The index is associated with a primary key constraint.
Filegroup is offline or read-only.
"You cannot drop a clustered index online if the index is disabled on a view, or contains text, ntext, image, varchar(max), nvarchar(max), varbinary(max), or xml columns in the leaf-level ...
1
There's an application that will create triggers for you - ApexSQL Audit. You can test the behavior then, but based on what you said, it's not a high transaction database, so there should be no problems. One more resource you can use to audit data changes is reading transaction logs.
3
Yes, you'll need to recreate the package in 2008. 2005/2008 packages can be upgraded to 2012, but downgrading doesn't work.
There is a codeplex project/utility that can move 2008 packages to 2005 by changing version numbers, and making small changes to the XML. Unfortunately this approach doesn't work with 2012, as the package format went through some big ...
1
An Oracle database uses two basic kinds of disk files - .dbf (data) and .ctl (control) files. You can also have .idx files (for indices). In most environments I've been in, these are located in /u01, /u02, /u03, and on, for however many /u0X's your DBAs had created.
On the other hand, many Oracle installations don't use filesystem files, they use raw disk ...
0
Another option would be a DDL trigger, which can capture index operations.
USE your_database;
GO
CREATE TRIGGER [IndexEventAudit]
ON DATABASE
FOR ALTER_INDEX, CREATE_INDEX, DROP_INDEX
AS
BEGIN
SET NOCOUNT ON;
DECLARE @e XML = EVENTDATA();
DECLARE @sql NVARCHAR(MAX)
= @e.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(MAX)');
...
1
There are 4 kinds of memory clerks in SQL Server.
Generic
Cache Store
Object Store
User store
memoryclerk_sqlgeneral is a general memory clerk responsible for a variety of non-specific sql activities whereas the memoryclerk_sqlbufferpool is the clerk dedicated solely to the buffer pool, since the buffer pool is a busy process.
Links:
SQLServer-DBA ...
2
There're a few good articles on Google that cover the basics. Best bet for your specific query might be this one:
In depth look at Memory
3
Whilst liasing with Microsoft on a different issue (paid support request) i happened to ask them about this & they confirmed the unhashed password is passed to the remote server but the mechanism in which the SQL Engine does this is "hidden and cannot be captured" - but suffice to say, the login is not done using the hash.
Here is there full response:
...
4
In addition to Thomas' answer, for the things like common lookup data and generic functions, you can use synonyms.
USE CentralDB;
GO
CREATE TABLE dbo.Dates(...);
GO
USE OtherDB;
GO
CREATE SYNONYM dbo.Dates
FOR CentralDB.dbo.Dates;
Now your other databases can treat these like first-class, local objects, but you only need to maintain one copy. Note ...
0
Try this one -
CREATE TABLE dbo.Medicines
(
ID BIGINT IDENTITY(1,1) PRIMARY KEY
, MedicineName nvarchar(50)
, BrandName nvarchar(50)
, MedicineCode nvarchar(20)
, Price DECIMAL(10,2)
, Quantity INT
)
CREATE NONCLUSTERED INDEX [ind_Medicines] ON [dbo].[Medicines]
(
[MedicineName] ASC,
[BrandName] ASC,
[MedicineCode] ASC
...
3
Using SELECT * is bad practice especially in a stored procedure. Even though you have a WHERE clause to filter the rows returned, I would explicitly state the columns.
As for indexes you will probably have to do the tuning yourself by looking at the execution plan for each type of index applied. However I would have a clustered index on ID and 1 non ...
4
Three things immediately spring to mind:
Master Data Services, part of the SQL Server arsenal.
Roll your own - build a central database server to store this data; have the other servers link to that server for this information. This matches your "single point of truth" description, but may not be entirely practical.
Use software to automate the rollout / ...
1
This means that database users have no matching server logins.
That is, each database sys.database_principals has no match in sys.server_principals
For Windows logins this is easy. This generates your missing CREATE LOGINS
USE MyDB
SELECT
'CREATE LOGIN ' + QUOTENAME(SUSER_SNAME(sid)) + ' FROM WINDOWS'
FROM
sys.database_principals DP
WHERE
...
0
I Agree With Thomas Rushton.
If you have few users Use the Following Process.
Remove the users excepting the [dbo], guest, INFORMATION_SCHEMA, [sys]
and Then Add your users again by right clicking on Users-->New User option
This Processes is doing the same thing what Mr Thomas has suggested but it is manual process.
4
You are lost on Auto Grow, you are lost on File Groups. For your own sake, I hope this is not a production DB.
Having said that - here is how the DB spacing works:
Execute this in SQL Server Management Studio
sp_helpdb <yourdbname>
This should give you 2 sets of output.The first has a column called db_size, which will give you the current DB size. ...
6
If your database has 16 MB of free space, and the drive it's on has plenty of free space, then don't worry about it. As long as there is sufficient space and you haven't disabled the database's ability to autogrow, then the database will grow the data file when it needs to. Of course you should set your data file's autogrow to some realistic increment, ...
0
The thing you're trying to fix is what's known as "Orphaned Users" - I blogged about this a while ago, at http://thelonedba.wordpress.com/2013/02/22/restoring-into-a-different-environment-security-problem-orphaned-users/
However, without seeing your exact error message, it's hard to help you with specifics.
0
right click on your database --> Tasks --> generate scripts -->next-->next-->
click on advanced button --> change the option "type Of Data to script" to "schema and data"
-->ok
-->next -->next
so now you have a script that creates database and its table and fills the data in it :) ;)
1
In SSMS object Explorer, click Management
Right-click Database Mail
Click Configure Database Mail
4.Select Set up Database Mail by performing the following tasks
5.Enter a profile name and click Add
6.Enter the settings for your email account. I could not set the account for my company's email account - it just didn't work. But I had no trouble ...
6
CASE is an expression that returns a single value. You are attempting to use it as control of flow logic to optionally include a filter, and it doesn't work that way. Maybe something like this would work:
AND COALESCE(Project.Contribution, 0) >= CASE @BudgetFilter
WHEN 0 THEN 25000 ELSE 2000000000 END
-- guessing that 2 billion is enough to always be ...
0
I am not sure you are setting up a SQL on VM or Physical. If it is VM, storage decision may vary as it is depends on the VM Host.
I recommend not to install SQL on C drive, dedicate the SQL for OS and OS only. So, it can be small but make sure to mirror the C drive, because if C drive give up whole sever needs to rebuilt.
As everyone suggest, RAID 10 much ...
3
Based on feedback in the comments on this answer, the situation is this:
There is an explicitly-created BUILTIN\Administrators group login in SQL Server that has been denied CONNECT to the database engine.
There is no other sysadmin-level login available, including the NT AUTHORITY\SYSTEM login that gets created by default.
SQL Server does not allow a ...
4
Follow the steps on Connect to SQL Server When System Administrators Are Locked Out. Start server with -m, connect as local admin, enable [sa], retsart server normally. Read the link.
Top 50 recent answers are included
