is there a setting that will ebnable uniform extent allocation upon
creation of index/table by default ?
if there isn't any default setting can you code it in?
thanks,
Doron
What causes SQL Server 2000 to create tables with default column sizes of 8000 for a varchar datatype??
I would assume this can cause significant performance problems.
(see attached)
Hi all,
I need to load a table with 820,000 records from a Sybase db via DTS. It always fail with the error:
"Error at destination for row number 820000. Could not allocate space for object in tablespace tempdb
. The default filegroup is full.".
There is only the primary filegroup defined in the db. I've increased the size from 1.5GB to 2GB,
and specify that it shd grow automatically by 10% and there's no limit to the size.
There is still some 28GB in the server, so it should be fine.
It still fail so I added another file to the primary filegroup with size 100MB. Again, it failed with the same error msg.
Where or how else can I increase the tempdb size?
Any help is appreciated.
Thanx,
Jes
I notice when I create a new database within my SQL Server that the
permissions for the new database automatically adds a user (Who is configured
as sysadmin) with dbo permissions to this database.
Both within the login properties of the select user (User mapping) is listed
as Default Schema, dbo and within the permissions of the database listed as
user with connect permissions.
I have other users configured as sysadmin and they do not get this rights
(They are not expliticly listed within user mapping with dbo or permissions
as user within the database).
I've inherited this system and wonder if the user has somehow changed the
new database procedure so it changes the default permissions of new databases.
Any way to check what he's done, I can see no differences between him and
the other sysadmins but he's definatly specifically listed as a dbo on all
new databases.
Although I don't mind him having access, he's a sysadmin after all, I'd like
to make it uniform thoughout the system, i.e. using the inhertited
permissions rather than specific permissions that seem to be created when the
new database is created.
Any ideas?
Flubster
I have installed my modell database onto c:mssql7data...
How do I now change the properties of this so that when someone creates a database, the path it will get created to is set to d:
I thought that I could just move model, but it seems that this isn't possible.
Any advice?
thanks
derek
Hi,
In my system, for creating a databae the default path is in C: drive, but iwant to change into D: drive as a default path.Please tell me how to change??
Looks like SQL decided to make an index, is this typical? Where can I look at the parameters for this? Thanks
From SQL Profiler:
create statistics hind_239_3 on [calljrnl] ( [cj_created_by] )
Hi,
like I said in other posts, I'm new to sql server, worked with informix for a long time. Right now I got into an "argument" with the present "dba", the indexes for all the tables in the database are being rebuilt, he wanted to start a monthly process, I told him that he can't do processing because when an index is built the table is locked, now, since I'm new at sql server I would like to know from the experts..... can you run process in sql server against tables when:
- indexes are being created
- the structure of a table is being modified
- the database is being shrunk
If we had been talking about informix then I would have probably slapped him if he tried to "teach" me about how things work, I'm pretty sure its the same in SQL but I think its better to have complete assureness of what I'm talking about before the slapping starts :)
Thanks in advance
Luis Torres
I have 2 questions,
1. I am trying to create an index, which is running for a long time. Is there an option to know the progress so far or how much more time it is going to take?
2. Is there anyway to find out how much space is required to rebuild an index?
------------------------
I think, therefore I am - Rene Descartes
Hi -
I could not find an SQL script, which would generate index creation DDL for a specific table/index. Query Analyzer does it, but I need to do it programmatically. Does anyone have a working script?
Thank you.
Does Microsoft SQL Server keep a record of an index creation date andlast modified date in a system table somewhere?
View 2 Replies View RelatedI am trying to create a new full text index on some table and getting the follwing error
"The Microsoft Search service
cannot be administered under the present user account"
i have already checked that Microsoft Search service is properly installed with the server version of SQL Server and this is running under the system account
could anyone plz comeout with the solution to this problem]
thanks in advance
bhavya
I was reading this blog below about Indexed Views and needed some clarification.
When the author choose to create the unique clustered index he excluded the PER.BusinessEntityID columns and only included, (CustomerID, SalesOrderID, ProductID).
Why didn't the PER.BusinessEntityID get added?
[URL] ....
CREATE VIEW Sales.vSalesSummaryCustomerProduct
WITH SCHEMABINDING
AS
SELECT CUST.CustomerID ,
SOH.SalesOrderID ,
SOD.ProductID ,
SUM(SOD.OrderQty) AS TotalOrderQty ,
[Code] .....
Hi
I'm trying to use a user defined alias type in a table creation, and everything works as expected, right until the moment where I rightclick on [Indexes] and click [new index] to create a new index.
Then I get this error:
TITLE: Microsoft SQL Server Management Studio
------------------------------
Cannot show requested dialog.
------------------------------
ADDITIONAL INFORMATION:
Cannot show requested dialog. (SqlMgmt)
------------------------------
Object reference not set to an instance of an object. (SqlManagerUI)
------------------------------
BUTTONS:
OK
------------------------------
This is the script I use to create the table:
exec sp_executeSql N'CREATE SCHEMA [TestSchema] AUTHORIZATION [dbo]'
GO
CREATE TYPE [TestSchema].[type_TestType]
FROM uniqueidentifier ;
GO
Create table [TestSchema].[Periode3]
(
Periode3_UID uniqueidentifier NOT NULL ROWGUIDCOL,
rowVersion timestamp NULL,
owner uniqueidentifier not null,
validFrom datetime
NOT NULL CONSTRAINT
DF_Periode_modified3 DEFAULT getdate()
);
Alter table [TestSchema].[Periode3]
add PeriodeType [TestSchema].[type_TestType];
now if I replace the 2 last lines with:
Alter table [TestSchema].[Periode3]
add PeriodeType uniqueidentifier;
everything works as expected.
Where did I go wrong ?
regards
Jens Chr
I need to find all the index and the creation date. I did cross apply of sys.objects & Sys.indexes on name column. I am getting some but the team is saying they created so many. Any other option to find Indexes and their creation date?
View 2 Replies View RelatedCan anyone suggest an appropriate strategy to create unique index's when a distributed database is used. As a bit of a background I have several SQL Server 2005 servers scattered around the country and I am trying to come up with a way to create a unique index i.e. one that hasn't been used in a particular table say Staff in any of the databases around the country.
Performance is an issue particularly as our network isn't that fantastic has anyone done something similar and achieved reasonable speeds?
Thanks ...
Hi,
Does anyone have any good tips for reducing the amount of disk space required for new clustered index creation.
I have just finished building a rather large table (48gbs) and durring the clustered index creation I came very close to running out of disk space.
What I am already doing:
1.Recovery Model simple
2. index padding 0
3. fillfactor 0
Any help would be appreciated
daLoonster
Script they use to generate indexes in SQL 2005.
I have 2 databases on a separate instance. I want to script out all indexes from database1 then execute it on database2.
How to accomplish this task efficiently.
I have a Performance issue in ssrs report , query takes execution time 1 to 2 min. When I add the non clustered index for one of the table columns which takes only 2 sec to execute the query. But as from my environment I couldn't able to add the index due to these changes will effect for all reports. Without adding indexes which takes 2 min to run that report. My question is:
Is there any way to add the non-clustered index on the report itself like if a non clustered index exists "execute the query" else create a non clustered index and run that report with newly created index in report itself like a temp table concept. How to add this concept in this below query:Final Query without non clustered index, takes 2 min to execution time ---
with cte1
( MovementDate,
MovementTypeDesc,
CDCNumber,
MovementTime,
LocationReportMovement,
OtherLocationCode,
[code]....
i created a query and when i run it like this i get data but when i add a value in the 2ed case for '2%' i get error.
Select a.email, case when a.reportnumber like '1%' then (select b.Reportnumber from ijasSummaryNo b where a.Reportnumber = b.Reportnumber) end as Reportnumber, case when a.Reportnumber like '1%' then (select b.stonebreakdown from ijasSummaryNo b where a.Reportnumber = b.Reportnumber) end as Measurement, case when a.Reportnumber like '1%' then (select b.reportcarddate from ijasSummaryNo b where a.Reportnumber = b.Reportnumber) end as ijasDate,
case when a.reportnumber like '2%' then (select c.Reportnumber from appraisalsummaryblue c where a.reportnumber = c.reportnumber) end as imacsRepNo
from t_RegisterInfoTemp a
Query works fine like this but when i add this (the one marked bold i get error)
case when a.reportnumber like '2%' then (select c.Reportnumber from appraisalsummaryblue c where a.reportnumber = c.reportnumber) end as imacsRepNo,case when a.reportnumber like '2%' then (select c.Measurement from appraisalsummaryblue c where a.reportnumber = c.reportnumber) end as Measurement2
This is the error.
Server: Msg 4414, Level 16, State 1, Line 1Could not allocate ancillary table for view or function resolution. The maximum number of tables in a query (260) was exceeded.
We are running SQL Server 2014 Enterprise Edition (64-Bit) on Windows 2012 R2 Standard (64-Bit).
1. When to create indexes, before or after data is added? Please address Clustered and Non-Clustered Indexes.
2. To move indexes to it's own filegroup, is it best to create the NON-Clustered Indexes on the separate filegroup with code similar to the example below?
CREATE NONCLUSTERED INDEX IX_Employee_OrganizationLevel_OrganizationNode
ON HumanResources.Employee (OrganizationLevel, OrganizationNode)
WITH (DROP_EXISTING = ON)
ON TransactionsFG1;
GO
I have read the following links that states that if you create the Clustered Index on a separate filegroup, it would also move the base table to that particular filegroup. (So I take it that you ONLY can move NON-CLustered Indexes to a separate filegroup.)
Placing Indexes on Filegroups:
[URL]
By default, indexes are stored in the same filegroup as the base table on which the index is created. A nonpartitioned clustered index and the base table always reside in the same filegroup. However, you can do the following:
• Create nonclustered indexes on a filegroup other than the filegroup of the base table.
Move an Existing Index to a Different Filegroup:
[URL]
Limitations and Restrictions
• If a table has a clustered index, moving the clustered index to a new filegroup moves the table to that filegroup.
• You cannot move indexes created using a UNIQUE or PRIMARY KEY constraint using Management Studio. To move these indexes use the CREATE INDEX statement with the (DROP_EXISTING=ON) option in Transact-SQL.
I have a very interesting problem in T-SQL coding for which I can't figure out the solution. Actually there is a Line_1_Address column in our data warehouse address table which is being populated from various sources. Some sources have already concatenated house number + street address fields in the Line_1_Address column whereas one source has separated columns for both data fields.
Now I'm trying to extract data from this data warehouse table and I need to split the house number from street address and load it into separate columns in my destination table. In case there is no data for house number then I should load it as NULL.
The issue is that data in this Line_1_Address column is very inconsistent so I don't know which functions to use. Here is some sample data for your consideration:
Line_1_Address
101 E Commerce ST
120 E Commerce ST
2 Po Box
301 W. Bel Air Ave
West Main Street, PO Box 1388
[Code] .....
We are using an application that stores it's data in SQL Server 2005. The application manages the creation of all it's own objects, including indexes. I would like to migrate the index data to a new filegroup, but there is no place in the application to specify how it builds it's indexes. I assume the indexes are created without specifying a filegroup so the default Primary is used. So, I'm wondering if there is a database option somewhere that you can set the default filegroup for index data?
If not, the only way I can think of accomplishing this goal is through DDL triggers to capture Create Index and Create Table statements and overwrite the Filegroup there.
Thanks in advance.
-Preston M. Price
Where should i start to try and fix some unalocated extents?
View 2 Replies View Relatedhi all!
i'm not sure that i understand how sql server stores data.
in documentation, i found that sql server stores data in pages.
every page have size of 8192 bytes (8060).
as i understand well, every table row is stored in one page. also, every table row can not be larger then one page. and that's where my confusion starts - because it sounds like we can not have data in table that is larger then 8060.
please, if you can you help me to understand this.
thank you in advance!
Hi all,
I have started to look at the way our production DB has been defined and set up, with the view to improving performance.
The DB is now 11gb, and the original size was set up as 3000mb, the rest has been take in 10% additional extents.
Now, back in my DB2 DBA days, this was a bad thing to have any data spread across extents as they may not be contiguous. I am assuming that is the same with SQL Server. Can someone confirm/deny this?
If this is the case, how can I get the DB back into one primary partition?
Thanks in advance.
Mike
When running the DBCC newalloc command I get some errors like the following:
"extent 51400 is in the wrong segment"
"chain processed with bad segment for object 448004627"
I've checked the system tables and system stored procedures and see no reference to page numbers being mapped to segments or identifying which extents are associated to which tables. I'm trying to determine which pages are associated with these extents so that I can print them to view the content. Any ideas how to determine the page numbers associated with extents and which extents are associated with each table?
I am reading the official documentation on pages and extents. I've also read an article on the official SQL Server Blog (sadly, I can't link as a new member). These articles do a good job explaining what an extent is.Why use extents at all?I can't find a good example anywhere of how grouping pages into extents make SQL Server work more efficiently. Any good example of what SQL Server would be missing if it didn't have extents at all, and how extents fix this?
View 6 Replies View RelatedHi,
I'm running a merge replication on a sql2k machine to 6 sql2k subscribers.
Since a few day's only one of the merge agents fail's with the following error:
The merge process could not retrieve generation information at the 'Subscriber'.
The index entry for row ID was not found in index ID 3, of table 357576312, in database 'PBB006'.
All DBCC CHECKDB command's return 0 errors :confused:
I'm not sure if the table that's referred to in the message is on the distribution side or the subscribers side? A select * from sysobjects where id=357576312 gives different results on both sides . .
Any ideas as to what is causing this error?
Hi everyone,
When we create a clustered index firstly, and then is it advantageous to create another index which is nonclustered ??
In my opinion, yes it is. Because, since we use clustered index first, our rows are sorted and so while using nonclustered index on this data file, finding adress of the record on this sorted data is really easier than finding adress of the record on unsorted data, is not it ??
Thanks
I'm looking for hints/tips/url for a script which can be run a few times per day and either sends an email or alert for the following condition:
Script would function like:
[URL]
Databases - Dynamic Log File Growths Remaining Alarm
Dynamic Log File Growths Remaining alarm becomes active when a non fixed size log file in any database is in danger of running out of space to grow. It is raised when a log file is almost full and the file cannot automatically grow enough to relieve the problem.
We have our databases with Enable Autogrowth (in Megabytes), and then a Maximum File Size (Limited to a MB value).
Example: If one of the database logs (or possibly filegroup primary) picks up another extent and is about 5 extents (arbitrary value) away from running out of growth room, an alert would be sent to an email address/profile.
P.S. Yes, there are multiple databases on this one instance and the script should loop to run through all of them.
I have multiple Common Table Expressions i.e CTE1,CTE2,...CTE5.
I want to Create Temperory Table and inserting data from CTE5. Is this possible to create?
like:
create table #temp (row_no int identity (1,1),time datetime, action varchar(5))
insert into #temp select * from cte5
Hi,
First time poster here, basically I have a second year university module on database design and for our coursework we have to model and create a database. One of the questions asks us to create a table that has a constraint on how many rows it can contain. I now that this is possible in some other databases, however I haven't seen a constraint that I could use on create table to limit the number of rows.. Does anyone now if this is possible?
Thanks,
Al