I have bunch of heap tables and the fragmentation seems to be high, i am not sure whether i shall add index for them, as these tables are inserted and updated every day.
View 4 RepliesWe have a database with a table that contains around 180m records. Each day a further 70k are inserted. No records are ever deleted as this table is used for archiving only.Users are required to perform SELECTs on this table constantly but due to the high number of INSERTs the indexes become very fragmented very quickly. My aim is to avoid daily rebuilds of the indexes which is what our software house is telling us we have to do.
This is the DDL for the table:
CREATE TABLE [dbo].[Inventory](
[EAN] [bigint] NOT NULL,
[Day] [smalldatetime] NOT NULL,
[State] [int] NOT NULL,
[Quantity] [int] NULL,
[StockValue] [float] NULL,
CONSTRAINT [PK_Inventory] PRIMARY KEY CLUSTERED
[code]...
There are also three clustered Indexes on this table each referencing a single column. The problem from my side is that I cannot understand why the three columns in a primary key would also be configured as non-clustered indexes.My solution would be one of the following:
1. Accept the tables are going to be fragmented and require a daily rebuild (don't like this one!)
2. Partition the table
3. Remove the non-clustered Indexes and let the clustered index for the primary key do the work.
Is there any way to restore a database of higher version to lower version.
E.g. I have created a database in sql server 2012, created some tables & procedures in that.I took Full backup of that database. Can I restore it to sql 2008r2 or any lower version.
I know direct restore is not possible, I have to use either import or export option or generating script,but i want to know is there any easy step to do so.
Vimal Lohani
SQL DBA | MCP (70-461,70-462)
Here is my table:
My question is: How can I insert a row for each unique TemplateId. So let's say I have templateIds like, 2,5,6,7... For each unique templateId, how can I insert one more row?
I am new in SQL Server, What to check ,what action do i need to take while adding a new column to a table.
View 5 Replies View RelatedPreviously same records exists in table having primary key and table having foreign key . we have faced 7 records were lost from primary key table but same record exists in foreign key table.
View 3 Replies View Relatedhow to take backup of indexes for a particular table.
View 1 Replies View RelatedHow to take backup of indexes for a particular table.
View 5 Replies View RelatedI'm having problems with the following code:
--DROP MASTER KEY
--GO
USE master;
CREATE MASTER KEY
ENCRYPTION BY PASSWORD = 'Pass@word1';
GO
USE master;
[code]....
What am I missing? What do I have to do if I get in a situation where I need to back out and start over?
[URL]
I'm getting a replication error that an object (Table) was not found. Any script that can capture this information?
View 3 Replies View RelatedI'm being asked to create multiple filegroups for a new database based on the table type, transaction, lookup, misc... From what i'm reading this doesn't make sense. I'm reading either large tables get file groups, nonclustered indexes when they are about the same size of the data, or a few other reasons...
First of all, we are talking about the same disk (please don't ask me about how it is configured) and I'm not sure yet if restoring separate file groups is even going to be necessary.
So here are my questions (beyond, the test and see what happens) because in the end I'm going to probably have to do what i'm told. So this is for my professional knowledge.
1. Does file groups separated by table type make sense?
2. Should you put tables that are queried often together in the same or different file groups.
3. I'm pretty sure you can't restore single file group for write access, am I correct?
I am trying to replicate data from a view in the publisher to a table in the subscriber (transaction replication). I do not need the view's base table, or the view itself, replicated to the subscriber. I only want to data from the view to feed a table in the subscriber.
Is this possible?
Running SQL Server 2008 R2 Enterprise.
With all the new functionality, can 2014 now restore a single table from a standard backup without using any third party tools? I have looked, but can't see this listed as a feature (though that doesn't mean it's not there, maybe I've just missed it).
View 6 Replies View RelatedI have a master table with after insert trigger on it.. When record is inserted into master table, the trigger fires and is captured in the backoffice table. In case the trigger fails, my record is neither in the master table nor in the back office table..
Is there anyway to capture the record either in the master table or in a separate table.
I am having an issue in determining the correct size of a table.
I have a tableA in some DB on transaction server (Enterprise Edition), this table is being replicated in reporting server DB (Standard edition).
When I check the space used by this table in both the databases i see noticeable difference.
I am using EXEC sp_spaceused 'tableA' to determine the space.
Transaction Server
------------------------------------------------------------------------------
name rows reserveddata index_size unused
TableA1439999 695416 KB507048 KB182912 KB 5456 KB
Reporting Server
-------------------------------------------------------------------------------
name rows reserveddata index_size unused
TableA1439999 656904 KB483664 KB172680 KB 560 KB
So I wanted to know what could be the possible reasons for this difference ?
I'm trying to find out what tables are being used in a Database.
I don't want the last User but the User and the Dates.
I have a script that return the last user but that is not going to work.
The following script returns the last user but not all users and the Login Name:
ITH LastActivity (ObjectID, LastAction) AS
(
SELECT object_id AS TableName,
last_user_seek as LastAction
FROM sys.dm_db_index_usage_stats u
WHERE database_id = db_id(db_name())
[Code] .....
I want to generate dynamic temp table so, from one strored procedure am getting an some feilds as shown below
CM_id,CM_Name,[Transaction_Month],[Transaction_Year],''[Invoice raised date],''[Payment Received date],''[Payout date],''[Payroll lock date]
for i want to generate table for the above feilds with datatype
I have created a stored procedure for retrieving column name, as shown below
CM_id, CM_Name, [Transaction_Month], [Transaction_Year], [Invoice raised date],[Payment Received date],[Payout date],[Payroll lock date]
Now I am trying to create a temporary table using the above generated coluimns from Stored Procedure with datatype.
I need to do some operations on a table when any user dont work with it.How do i do it?Do i lock the table? and is it possible?
View 8 Replies View RelatedI have a job scheduled that imports a table from a Oracle database. The job runs at 3am and reports success. But for some reason when i query the table to see how many records there are, I see the same row count as the day before (it should increase everyday- student enrollment). When i execute the package manually, the table updates fine.
SQL Server 2014/Win7 x64.
I know select * into ##tmp1 should work but I get a database connection error.
View 9 Replies View RelatedIn the Operating environment databases, may be made tables in the database on a temporary basis but they are still yet and they are not removed, how to identify tables that have been made on a temporary basis are not used (don’t have any read & write records)?
View 4 Replies View RelatedThe error is "The table with Name of 'Table Name' does not exist.An error occurred when loading the Model."
We get this error when we try to check the properties of an analysis server using SQL Server Management studio. We have resolved this issue twice by Stopping the SQL Server analysis service,removing db folders from Analysis Server Data folder and starting the services back on. The db folder that we removed was advised by the BI team.
The SQL Server Analysis Server is 2012 SP1
Is there any way to enforce table references in stored procedures? For Example, we have stored procedures with a ton of different formats, "dbo.table", "table", "db.dbo.table", etc. Can we make it so that for every stored procedure, the reference must be at least "dbo.table"?
View 1 Replies View RelatedI am setting up extended events more or less just fine, however I am a bit confused as to how to read and load them into a table for querying. In particular the offset part - is there a way to load just a given dates worth in?
I've got the files configured to be 20MB before rolling over, the XE is running all the time.
So if i load in the full file now, say that covers 2.5 days worth, when I load it again tomorrow to get the updated data I'm also reloading today, which is a waste?
I presume I am going about this wrong, but lack an example that really goes into detail of practicalities of loading this data.
How can I easily identify who dropped a table?
View 8 Replies View RelatedI want to set up a database role so that users can use sp_readerrorlog through SSMS. It does a check on membership in the securityadmin role.
I have tested it and can see you can grant execute on xp_readerrorlog but the SSMS GUI uses sp_readerrorlog.
I thought I could create a user/certificate and add the signature to sp_readerrorlog but it's not permitted (likely because it's not a normal database object).
So the other solution is to add the users to the securityadmin role but then explicitly deny alter any login (best done with a custom server role in 2012+ but otherwise just manually in 2008). I tested this out and it works, I'm not able to alter any logins or increase my own permissions, I also did a check of what's reported from fn_my_permissions(null, null) and it shows minimal permissions like I'd expect.
I try to load data into a memOpt table (INSERT INTO ... SELECT ... FROM ...). The source table has a size about 1 Gb and 13 Mio Rows. During this load the LDF File grows to size of 350 GB (until the space if the disk is run out of space). The Server has about 110 GB Memory for the SQL Server reserved. The tempdB doesn't grow. The Bucket Size in the create statement has a size of 262144. The Hash key as 4 fields`(2 fields have the datatype int,1 has smallint, 1 has varchar(200). ) The disk for the datafiles has still space for the datafiles (incl. the hekaton files).
How can I reduce the size of the ldf files during the load of the data ?
I am looking for a table where Maintenance Clean Up Task configuration is stored. For example, Delete file older than the following - which is 2 days. Which table can I retrieve the setting in msdb ?
View 5 Replies View RelatedIt is possible to find table size and in that table each row size.
View 4 Replies View RelatedI inherited a lot of Servers to upgrade to 2014 to include an SSRS Server.
The encryption Key was never backed up and it seems that no one knows what the password is?
Do I have to manually load the reports? There are a lot of Reports.
[URL]
I want to use BCP to load data from a text file.
By default, constraints are turned off in bcp, so I use the CHECK_CONSTRAINTS hint.
bcp aborts if ANY of the rows contains a FK violation. No data get loaded.
So if I add the -b 1 batch size option, it loads all data UNTIL the first FK violation, but nothing after that.
I want to load EVERYTHING ... except for the violations. But bcp won't let me. Is there a way?
If I install an instance with Windows Only authentication, and then change it to Mixed Mode, if I enable the sa login, the password has already been set. What is the default? If it's generated, how secure is it? Is the password generated? What algorithm is used for that?
View 9 Replies View Related